package com.techthinker.dreamBuilder.Dictionary;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.techthinker.dreamBuilder.Database.DatabaseHelper;
import com.techthinker.dreamBuilder.Define.Constant;
import com.techthinker.dreamBuilder.Entity.Dictionary;
import com.techthinker.dreamBuilder.Entity.Lists;

public class ListDAL {
	// private static final String TAG = "ListsService";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;
	private static final String KEY_DICTIONARY_ID = "dictionary_id";
	private static final String KEY_LIST_NUM = "list_num";
	private static final String KEY_NAME = "name";
	private static final String KEY_WORDSNUM = "wordsNum";
	private static final String KEY_ROWID = "list_id";

	public ListDAL(Context ctx) {
		this.mCtx = ctx;
	}

	public ListDAL open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx, Constant.DB_NAME);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public boolean isClosed() {
		if (mDbHelper == null)
			return true;
		else
			return false;
	}

	// create a list item
	public long createLists(Lists list) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_DICTIONARY_ID, list.getDictionary()
				.getDictionary_id());
		initialValues.put(KEY_LIST_NUM, list.getList_num());
		initialValues.put(KEY_NAME, list.getName());
		initialValues.put(KEY_WORDSNUM, list.getWordsNum());
		return mDb.insert(Constant.TABLE_LIST, null, initialValues);
	}

	// update a list item
	public boolean updateLists(Lists list) {
		ContentValues args = new ContentValues();
		args.put(KEY_DICTIONARY_ID, list.getDictionary().getDictionary_id());
		args.put(KEY_LIST_NUM, list.getList_num());
		args.put(KEY_NAME, list.getName());
		args.put(KEY_WORDSNUM, list.getWordsNum());
		return mDb.update(Constant.TABLE_LIST, args,
				KEY_ROWID + "=" + list.getList_id(), null) > 0;
	}

	// delete a list item and it's words
	public boolean deleteLists(Lists list) {
		return deleteLists(list.getList_id());
	}

	// delete a list item and it's words
	public boolean deleteListsByDictionary(int dictionary_id) {
		// fetch the related list
		WordDAL wordDAL = new WordDAL(mCtx);
		wordDAL.open();
		wordDAL.deleteWordsByDictionary(dictionary_id);
		wordDAL.close();
		return mDb.delete(Constant.TABLE_LIST, KEY_DICTIONARY_ID + "=" + dictionary_id, null) > 0;
	}

	// delete a list item and it's words
	public boolean deleteLists(int list_id) {
		return mDb.delete(Constant.TABLE_LIST, KEY_ROWID + "=" + list_id, null) > 0;
	}

	// retrieve all lists
	public List<Lists> fetchAllListss() {
		Cursor mCursor = mDb.query(Constant.TABLE_LIST, new String[] {
				KEY_ROWID, KEY_DICTIONARY_ID, KEY_LIST_NUM, KEY_NAME,
				KEY_WORDSNUM }, null, null, null, null, null);
		List<Lists> alllists = new ArrayList<Lists>();
		// fetch the related dictionary
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		Dictionary dictionary = dicDAL.fetchDictionaryById(mCursor
				.getInt(mCursor.getColumnIndex(KEY_DICTIONARY_ID)));
		dicDAL.close();
		while (mCursor.moveToNext()) {
			Lists list = new Lists();
			list.setList_id(mCursor.getInt(mCursor.getColumnIndex(KEY_ROWID)));
			list.setDictionary(dictionary);
			list.setList_num(mCursor.getInt(mCursor
					.getColumnIndex(KEY_LIST_NUM)));
			list.setName(mCursor.getString(mCursor.getColumnIndex(KEY_NAME)));
			list.setWordsNum(mCursor.getInt(mCursor
					.getColumnIndex(KEY_WORDSNUM)));
			alllists.add(list);
		}
		mCursor.close();
		return alllists;
	}

	
	// retrieve all lists to cursor
	public Cursor fetchAllListssToCursor() {
		return mDb.query(Constant.TABLE_LIST, new String[] { KEY_ROWID,
				KEY_DICTIONARY_ID, KEY_LIST_NUM, KEY_NAME, KEY_WORDSNUM },
				null, null, null, null, null);
	}
	// retrieve all lists
		public List<Lists> fetchAllListssByDictionaryId(int dictionary_id) {
			Cursor mCursor = mDb.query(Constant.TABLE_LIST, new String[] {
					KEY_ROWID, KEY_DICTIONARY_ID, KEY_LIST_NUM, KEY_NAME,
					KEY_WORDSNUM }, KEY_DICTIONARY_ID + "=" + dictionary_id, null, null, null, null);
			List<Lists> alllists = new ArrayList<Lists>();
			// fetch the related dictionary
			DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
			dicDAL.open();
			Dictionary dictionary = dicDAL.fetchDictionaryById(dictionary_id);
			dicDAL.close();
			while (mCursor.moveToNext()) {
				Lists list = new Lists();
				list.setList_id(mCursor.getInt(mCursor.getColumnIndex(KEY_ROWID)));
				list.setDictionary(dictionary);
				list.setList_num(mCursor.getInt(mCursor
						.getColumnIndex(KEY_LIST_NUM)));
				list.setName(mCursor.getString(mCursor.getColumnIndex(KEY_NAME)));
				list.setWordsNum(mCursor.getInt(mCursor
						.getColumnIndex(KEY_WORDSNUM)));
				alllists.add(list);
			}
			mCursor.close();
			return alllists;
		}
	// retrieve a list item by id
	public Lists fetchListsById(long rowId) throws SQLException {

		Cursor mCursor = mDb.query(true, Constant.TABLE_LIST, new String[] {
				KEY_ROWID, KEY_DICTIONARY_ID, KEY_LIST_NUM, KEY_NAME,
				KEY_WORDSNUM }, KEY_ROWID + "=" + rowId, null, null, null,
				null, null);
		// fetch the related dictionary
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		Dictionary dictionary = dicDAL.fetchDictionaryById(mCursor
				.getInt(mCursor.getColumnIndex(KEY_DICTIONARY_ID)));
		dicDAL.close();
		if (mCursor.moveToFirst()) {
			Lists list = new Lists();
			list.setList_id(mCursor.getInt(mCursor.getColumnIndex(KEY_ROWID)));
			list.setDictionary(dictionary);
			list.setList_id(mCursor.getInt(mCursor.getColumnIndex(KEY_ROWID)));
			list.setList_num(mCursor.getInt(mCursor
					.getColumnIndex(KEY_LIST_NUM)));
			list.setName(mCursor.getString(mCursor.getColumnIndex(KEY_NAME)));
			list.setWordsNum(mCursor.getInt(mCursor
					.getColumnIndex(KEY_WORDSNUM)));
			mCursor.close();
			return list;
		}
		mCursor.close();
		return null;
	}
}
